package com.projectbox.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import com.projectbox.dao.ProjectDao;
import com.projectbox.model.Project;
import com.projectbox.model.User;


public class JdbcProjectDao implements ProjectDao {

	private Logger logger = LoggerFactory.getLogger(getClass());

	private SimpleJdbcTemplate simpleJdbcTemplate;
	private SimpleJdbcInsert insertProjectTemplate;
	private SimpleJdbcInsert insertAccessTemplate;

	public void setDataSource(DataSource dataSource) {
		this.simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
		this.insertProjectTemplate = new SimpleJdbcInsert(dataSource)
				.withTableName("projects").usingGeneratedKeyColumns("id");
		this.insertAccessTemplate = new SimpleJdbcInsert(dataSource)
				.withTableName("accesses").usingGeneratedKeyColumns("id");
	}

	public void insert(Project project) {
		logger.debug("insert new project: " + project);

		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("name", project.getName());
		parameters.put("description", project.getDescription());
		parameters.put("privacy", project.getPrivacy());
		parameters.put("mid", project.getMid());
		parameters.put("time_created",project.getTimeCreated().longValue());
		parameters.put("time_modified",project.getTimeCreated().longValue());
		Number id = insertProjectTemplate.executeAndReturnKey(parameters);
		project.setId(id.longValue());
	}
	
	/**
	 * Updates the project name and description only
	 * MUST BE MODIFIED FOR ADMIN USE
	 */
	public void update(Project project) {
		String sql = "update project SET name=" + project.getName()
				+ ", description=" + project.getDescription() + " where id = ?";
		simpleJdbcTemplate.update(sql, project.getId());

	}

	public void delete(Long pid) {
		String sql = "delete from projects where id = ?";
		simpleJdbcTemplate.update(sql, pid);
	}

	public Project getProjectById(Long pid) {
		String sql = "select * from projects where id = ?";
		Project project = null;
		try {
			project = this.simpleJdbcTemplate.queryForObject(sql,
				new ProjectMapper(), pid);
		} catch (Exception e) {
			return null;
		}
		return project;
	}
	
	public Project getProjectByName(String name) {
		String sql = "select * from projects where name = ?";
		Project project = null;
		try {
			project = this.simpleJdbcTemplate.queryForObject(sql,
				new ProjectMapper(), name);
		} catch (Exception e) {
			return null;
		}
		return project;
	}
	
	/**
	 * Returns a list of all Projects
	 */
	public List<Project> getAllProjects() {
		String sql = "select * from projects";
		List<Project> projects = simpleJdbcTemplate.query(sql,
				new ProjectMapper());

		return projects;

	}

	public List<Project> getProjectsByManager(Long mid) {
		String sql = "select * from projects where mid = ?";
		List<Project> projects = simpleJdbcTemplate.query(sql,
				new ProjectMapper(), mid);

		return projects;
	}

	public List<Project> getContributedProjects(Long uid) {
		String sql = "select p.id, p.name, p.description, p.privacy, p.time_created, p.time_modified, p.mid "
				+ "from accesses ac, projects p where ac.uid=? and p.id=ac.pid and ac.status='granted'";
		List<Project> projects = simpleJdbcTemplate.query(sql,
				new ProjectMapper(), uid);
		return projects;
	}
	public List<Project> getNoncontributedProjects(Long uid) {
		String sql = "select * from projects p "
				+ "where p.id not in (select ac.pid from accesses ac where ac.uid = ? and ac.status='granted') and p.mid != ?";
		List<Project> projects = simpleJdbcTemplate.query(sql,
				new ProjectMapper(), uid, uid);
		return projects;
	}

	public List<User> getProjectContributors(Long pid) {
		String sql = "select usr.id, usr.username, firstname, lastname "
				+ "from accesses ac, users usr where ac.pid=? and usr.id=ac.uid and ac.status='granted'";
		List<User> users = simpleJdbcTemplate.query(sql, new UserMapper(), pid);

		return users;
	}
	
	public List<User> getProjectPendingUsers(Long pid) {
		String sql = "select usr.id, usr.username, firstname, lastname "
				+ "from accesses ac, users usr where ac.pid=? and usr.id=ac.uid and ac.status='pending'";
		List<User> users = simpleJdbcTemplate.query(sql, new UserMapper(), pid);

		return users;
	}

	public String getAccessStatus(Long uid, Long pid) {
		String sql = "select status from accesses where uid=? and pid=?";
		String status = null;

		try {
			status = this.simpleJdbcTemplate.queryForObject(sql, String.class,
					uid, pid);
		} catch (EmptyResultDataAccessException e) {
			return null;
		}

		return status;
	}

	public void saveAccess(Long uid, Long pid, String status) {

		if (this.getAccessStatus(uid, pid) != null) {
			String sql = "update accesses set status=? where uid=? and pid=?";
			this.simpleJdbcTemplate.update(sql, status, uid, pid);
		} else {
			Map<String, Object> parameters = new HashMap<String, Object>();
			parameters.put("uid", uid);
			parameters.put("pid", pid);
			parameters.put("status", status);

			this.insertAccessTemplate.execute(parameters);
		}
	}

	public void deleteAccess(Long uid, Long pid) {
		String sql = "delete from accesses where uid=? and pid=?";
		this.simpleJdbcTemplate.update(sql, uid, pid);
	}

	/**
	 * RowMapper for Project objects.
	 */
	private static final class ProjectMapper implements RowMapper<Project> {
		public Project mapRow(ResultSet rs, int rowNum) throws SQLException {
			Project project = new Project();
			project.setId(rs.getLong("id"));
			project.setName(rs.getString("name"));
			project.setDescription(rs.getString("description"));
			project.setPrivate(rs.getBoolean("privacy"));
			project.setMid(rs.getLong("mid"));
			project.setTimeCreated(rs.getLong("time_created"));
			project.setTimeModified(rs.getLong("time_modified"));
			return project;
		}
	}

	/**
	 * RowMapper for User objects.
	 */
	private static final class UserMapper implements RowMapper<User> {
		public User mapRow(ResultSet rs, int rowNum) throws SQLException {
			User user = new User();
			user.setId(rs.getLong("id"));
			user.setUsername(rs.getString("username"));
			user.setFirstName(rs.getString("firstname"));
			user.setLastName(rs.getString("lastname"));
			return user;
		}
	}

}
